package deweb.cimaplanning.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

import deweb.cimaplanning.misc;
import deweb.cimaplanning.dto.POPlantCosts;

public class POPlantCostDao extends DBDAO {
	/** Datasource name */
	private static final String dsName = misc.DS_NAME;//$NON-NLS-1$
	
	/**SQL Select all cost elements*/
	protected static final String SQL_SELECT_ALL_ELEMENTS = "SELECT pov.purchase_order_version_id, cp.plant_version_id, p.plant_id, p.priority, "
															+ "		pov.biweek_period_version_id, pv.month_period_version_id,  "
															+ "		SUM(cp.cost) amount, cp.tons "
															+ "	FROM costs_po_in_plants cp "
															+ "		INNER JOIN costs_elements ce "
															+ "			ON cp.cost_element_id = ce.cost_element_id "
															+ "		INNER JOIN plants_versions pv "
															+ "			ON cp.plant_version_id = pv.plant_version_id "
															+ "		INNER JOIN plants p "
															+ "			ON pv.plant_id = p.plant_id "
															+ "		INNER JOIN vw_v_purchases_orders_versions pov "
															+ "			ON cp.purchase_order_version_id = pov.purchase_order_version_id "
															+ "		LEFT OUTER JOIN transports t "
															+ "			ON pov.transport_id = t.transport_id ";

	/**SQL Select all cost elements group by*/
	protected static final String SQL_SELECT_ALL_ELEMENTS_GROUP_BY =  "	GROUP BY pov.purchase_order_version_id, cp.plant_version_id, p.plant_id, p.priority,  "
																		+ "		pov.biweek_period_version_id, pv.month_period_version_id, cp.tons, "
																		+ "		pov.is_transit, pov.transit_date, t.priority ";
	
	/**SQL Select all cost elements order by*/
	protected static final String SQL_SELECT_ALL_ELEMENTS_ORDER_BY =  "	ORDER BY pov.biweek_period_version_id, pv.month_period_version_id,  "
																		+ "		pov.is_transit DESC, pov.transit_date, t.priority DESC,  "
																		+ "		p.priority DESC ";
	
	/**
	 */
	public ArrayList getArray(String filters) throws Exception {
		StringBuffer sql = null;
		POPlantCosts dto = null;
		ArrayList array	= null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try{
			array = new ArrayList(100);
			
			//la versionId viene integrada en el filters
			
			sql = new StringBuffer(SQL_SELECT_ALL_ELEMENTS);
			
			if (filters.length() > 0){
				sql.append(SQL_WHERE);
				sql.append(filters);
			}
			
			sql.append(SQL_SELECT_ALL_ELEMENTS_GROUP_BY);
			sql.append(SQL_SELECT_ALL_ELEMENTS_ORDER_BY);
			
			pstm = prepareSQL(sql.toString(), dsName);
			
			rs = executeQuery(pstm);
			
			while (rs.next()){
				dto = new POPlantCosts();
				
				dto.setAmount(rs.getDouble("amount"));
				dto.setBiweekPeriodVersionId(rs.getInt("biweek_period_version_id"));
				dto.setMonthPeriodVersionId(rs.getInt("month_period_version_id"));
				dto.setPlantId(rs.getInt("plant_id"));
				dto.setPlantVersionId(rs.getInt("plant_version_id"));
				dto.setPriority(rs.getInt("priority"));
				dto.setPurchaseOrderVersionId(rs.getInt("purchase_order_version_id"));
				dto.setTons(rs.getDouble("tons"));
								
				array.add(dto);
			}
			
			array.trimToSize();
			
		}catch (PoolException poolException){
			Exception e = null;
			poolException.printStackTrace(System.out);
			e = new Exception(poolException);
			throw e;
		} catch(SQLException sqle){
			Exception e = null;
			sqle.printStackTrace(System.out);
			e = new Exception(sqle);
			throw e;
		}finally {
			try{ closeConnection(pstm.getConnection());	}catch(Exception exception){}
		}	
		
		return array;		
	}
}
